One of the most difficult aspects of database query optimization is the balancing act necessary to find the best strategy to run the query without taking too long to find it. A query optimizer is a component of a database management system that attempts to determine the most efficient way to execute a query. The output of an optimizer is typically referred to as a query plan or access plan, which is a form of executable code that can be processed by a database engine to execute the query.
An optimizer often uses a repository, herein denoted as a plan cache, to store the query plans generated for optimized queries. By storing query plans for certain queries, often the stored query plans may be used to execute later queries that are similar or identical to the queries for which the query plans were originally generated, thus eliminating the need to generate new plans for those later queries.
The objects in a plan cache persist until a reboot or restart occurs, or in case of a system failure, until the failover switch to a backup system occurs. It has also been found that optimizer plan caches also store large amounts of valuable optimization data that can be used by new queries to improve performance and avoid “warm-up” effects. “Warm-up” effects are related to additional processing time that is incurred by optimization, statistics collection, building of temporary objects, and so on. Because plan caches contain much of the statistic and optimization information for individual queries, much of this “warm-up” effect can be avoided by using a plan, statistics, or a temporary object from the plan cache.
Many installations often have multiple servers for production, development, high availability, etc. Moreover, these systems often maintain up to date versions of production data, e.g., for disaster recovery solutions on a high availability system. However, query optimization data, such as data generated during the optimization of a query, is often not backed up on a backup system, nor is any of the query optimization data typically generated during optimization of a query ever generated on a backup system. Backup systems typically do not run many of the queries that are run on a production system, and as such, no query optimization data is typically ever generated on backup systems. As a result, to the extent that plan caches may be backed up on backup systems, the plan caches on backup systems are usually not current and would likely cause new queries executed on the backup system to encounter the “warm-up” effect.
What is needed therefore is an automated method to preserve and update optimization data in the query plan caches on backup systems.